This customization example refers to scripts and samples available through ASI training.
1. Create a CampaignHistory table.
□ Open SQL Server Enterprise Manager or Query Analyzer.
□ Create a new CampaignHistory table, as this script illustrates:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CampaignHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CampaignHistory]
GO
CREATE TABLE [dbo].[CampaignHistory] (
[CampaignHistoryKey] [uniqueidentifier] NOT NULL ,
[CampaignKey] [uniqueidentifier] NOT NULL ,
[PropertyName] [nvarchar] (50) NULL ,
[OriginalValue] [nvarchar] (250) NULL ,
[CurrentValue] [nvarchar] (250) NULL ,
[UpdatedOn] [datetime] NULL ,
[UpdatedByUserKey] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
2. Create a CampaignHistory business object.
□ Open BOD.
□ Create a new CampaignHistory business object.
□ Add the CampaignHistory table.
□ Create properties from the Physical/Columns tab.
□ Add them to the display set.
□ Save, Build, and Publish the object.
3. Add a new Update History action to the Campaign business object.
□ Open BOD.
□ Open the Campaign business object.
□ Select the Actions tab.
□ Create a new action named "Update History". Be sure it is Active.
□ Under Triggering Events, select the checkboxes in the Before Commit row under On Add/New and On Update.
□ On the Code tab, add the following code:
int result = 0;
string notDisplayed = "(" + Asi.ResourceManager.GetPhrase("NotDisplayed", "Not Displayed") + ")";
// Loop through business properties for the committing business item
for(int x=0, xc=item.BusinessController.Columns.Count; x<xc; x++)
{
// Get the business property represented by the currently indexed column
BusinessProperty bp = (BusinessProperty)item.BusinessController.Columns[x];
// Determine if it's a property we don't want to track
if(bp.Name.Equals("UpdatedByUserKey") || bp.Name.Equals("UpdatedOn"))
continue;
bool displayValue = true;
// Determine if it's a property whose value we don't want to display
if((bp.SqlDbDataType.Equals(SqlDbType.Text)) || (bp.SqlDbDataType.Equals(SqlDbType.Binary)))
displayValue = false;
bool changed = false;
string originalVal = null;
string currentVal = null;
if((item[x, DataRowVersion.Original] == null))
{
if(item[x, DataRowVersion.Current] != null)
{
changed = true;
originalVal = "Null";
if (displayValue)
currentVal = item[x, DataRowVersion.Current].ToString();
else
currentVal = notDisplayed;
}
}
else if((item[x, DataRowVersion.Current] == null))
{
if(item[x, DataRowVersion.Original] != null)
{
changed = true;
if (displayValue)
originalVal = item[x, DataRowVersion.Original].ToString();
else
originalVal = notDisplayed;
currentVal = "Null";
}
}
else if(!item[x, DataRowVersion.Original].Equals(item[x, DataRowVersion.Current]))
{
changed = true;
if (displayValue)
{
originalVal = item[x, DataRowVersion.Original].ToString();
currentVal = item[x, DataRowVersion.Current].ToString();
}
else
{
originalVal = notDisplayed;
currentVal = notDisplayed;
}
}
// Determine if the value of the property's value has changed.
if(changed)
{
// Make sure values will fit in columns
if(originalVal.Length > 250)
originalVal = "Value too large for column.";
if(currentVal.Length > 250)
currentVal = "Value too large for column.";
// Construct an insert statement to create a new history record
StringBuilder sb = new StringBuilder();
sb.Append("INSERT vBoCampaignHistory (CampaignHistoryKey, CampaignKey, PropertyName, OriginalValue, CurrentValue, UpdatedOn, UpdatedByUserKey)");
sb.Append(" VALUES ");
sb.Append("(@campaignHistoryKey,@campaignKey,@propertyName,@originalValue,@currentValue,getdate(),@updatedByKey)");
DataParameter[] param = new DataParameter[6];
param[0] = new DataParameter();
param[0].SqlDbType = SqlDbType.UniqueIdentifier;
param[0].ParameterName = "campaignHistoryKey";
param[0].Value = Guid.NewGuid();
param[1] = new DataParameter();
param[1].SqlDbType = SqlDbType.UniqueIdentifier;
param[1].ParameterName = "campaignKey";
param[1].Value = (Guid)item["CampaignKey"];
param[2] = new DataParameter();
param[2].SqlDbType = SqlDbType.NVarChar;
param[2].ParameterName = "propertyName";
param[2].Value = ((BusinessProperty)item.BusinessController.Columns[x]).Caption;
param[3] = new DataParameter();
param[3].SqlDbType = SqlDbType.NVarChar;
param[3].ParameterName = "originalValue";
param[3].Value = originalVal;
param[4] = new DataParameter();
param[4].SqlDbType = SqlDbType.NVarChar;
param[4].ParameterName = "currentValue";
param[4].Value = currentVal;
param[5] = new DataParameter();
param[5].SqlDbType = SqlDbType.UniqueIdentifier;
param[5].ParameterName = "updatedByKey";
param[5].Value = (Guid)item["UpdatedByUserKey"];
// Insert the history row
result = dataServer.ExecuteNonQuery(CommandType.Text, sb.ToString(), param);
}
}
4. Test your work by creating a change to a campaign and querying your object:
□ In iMIS, navigate to Marketing > Campaign Management.
□ Select a campaign from the list, or enter a new one if needed.
□ Edit the campaign. For example, change values on the Definition tab, such as Description or End Date.
□ Save the record.
□ Open System Setup.
□ In the Scratch folder, create a query with the CampaignHistory business object as its source.
□ Run the query and note the rows corresponding to the changes you made to the campaign record.